package com.zyh.dao;
import com.zyh.entity.User;
import com.zyh.utils.TransactionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    public User login(String username, String password) throws SQLException {
        String sql = "SELECT user_id, username, user_type, bio FROM users WHERE username=? AND password=?";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return new User(
                        rs.getInt("user_id"),
                        rs.getString("username"),
                        null,
                        null,
                        null,
                        null,
                        null,
                        null,
                        rs.getString("bio"),
                        rs.getString("user_type")
                );
            }
            return null;
        }
    }

    public User selectById(Integer userId) throws SQLException {
        String sql = "SELECT user_id, username, email, bio FROM users WHERE user_id=?";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return new User(
                        rs.getInt("user_id"),
                        rs.getString("username"),
                        rs.getString("email"),
                        rs.getString("bio")
                );
            }
            return null;
        }
    }

    public void add(User user) throws SQLException {
        String sql = "INSERT INTO users(username, password, email, created_at) VALUES(?, ?, ?, NOW())";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            ps.executeUpdate();
        }
    }

    public List<User> selectByPage(int limit, int offset) throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT user_id, username, email FROM users ORDER BY user_id LIMIT ? OFFSET ?";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, limit);
            ps.setInt(2, offset);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                users.add(new User(
                        rs.getInt("user_id"),
                        rs.getString("username"),
                        rs.getString("email"),
                        null
                ));
            }
        }
        return users;
    }

    public List<User> selectAll() throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT user_id, username, email, bio FROM users ORDER BY user_id";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                users.add(new User(
                        rs.getInt("user_id"),
                        rs.getString("username"),
                        rs.getString("email"),
                        rs.getString("bio")
                ));
            }
        }
        return users;
    }

    public boolean existsUsername(String username) throws SQLException {
        String sql = "SELECT user_id FROM users WHERE username=?";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            return rs.next();
        }
    }

    public void deleteById(Integer userId) throws SQLException {
        String sql = "DELETE FROM users WHERE user_id=?";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.executeUpdate();
        }
    }

    public void updateById(Integer userId, String newEmail) throws SQLException {
        String sql = "UPDATE users SET email=? WHERE user_id=?";
        Connection conn = TransactionManager.getCurrentConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newEmail);
            ps.setInt(2, userId);
            ps.executeUpdate();
        }
    }
}
